﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using ManpowerOrg.Bean;

namespace ManpowerOrg.Data
{
	public class MPMRequestData
	{
		public  List<ManpowerRequest> Search()
		{
			var config = ConfigurationManager.ConnectionStrings["Manpower_Database"];
			var connString = config.ConnectionString;
			var listMpmRequest = new List<ManpowerRequest>();
			using (var con = new SqlConnection(connString))
			{
				con.Open();
				using (var command = new SqlCommand("select * from test_tt", con))
				{
					var reader = command.ExecuteReader();
					while (reader.Read())
					{
						var mpmRequest = new ManpowerRequest
						    {
						        MpmReqId = Convert.ToInt32(reader["mpm_req_id"]),
						        MpmDeptId = Convert.ToInt32(reader["mpm_dept_id"]),
						        MpmReqNo = reader["mpm_req_no"].ToString(),
						        MpmReqDate = Convert.ToDateTime(reader["mpm_req_date"]),
						        MpmReqStatus = Convert.ToInt32(reader["mpm_req_status"]),
						        MpmPosReqRemark = reader["mpm_pos_req_remark"].ToString(),
								MpmPosReqBudget = Convert.ToInt32(reader["mpm_pos_req_budget"]),
								MpmPosReqAmount = Convert.ToInt32(reader["mpm_pos_req_amount"]),
								MpmPosId = Convert.ToInt32(reader["mpm_pos_id"]),
								MpmBudTypeId = Convert.ToInt32(reader["mpm_bud_type_id"]),
								MpmReqDeptId = Convert.ToInt32(reader["mpm_req_dept_id"]),
								CreatedBy = Convert.ToInt32(reader["created_by"]),
								CreatedDate = Convert.ToDateTime(reader["created_date"]),
								UpdatedBy = Convert.ToInt32(reader["updated_by"]),
								UpdatedDate = Convert.ToDateTime(reader["updated_date"])
						    };
						listMpmRequest.Add(mpmRequest);
					}
				}
			}
			return listMpmRequest;
		}

		public void InsertMpmRequestData(ManpowerRequest mpmRequest)
		{
			var config = ConfigurationManager.ConnectionStrings["LocalDB"];
			var connString = config.ConnectionString;
			using (var con = new SqlConnection(connString))
			{
				con.Open();
				using (var command = new SqlCommand(
				"Insert Into test_tt (mpm_req_id,mpm_dept_id,mpm_req_no,mpm_req_date,mpm_req_status,mpm_pos_req_remark,mpm_pos_req_budget,mpm_pos_req_amount,mpm_pos_id,mpm_bud_type_id,mpm_req_dept_id,created_by,created_date,updated_by,updated_date) values (@MpmReqId,@MpmDeptId,@MpmReqNo,@MpmReqDate,@MpmReqStatus,@MpmPosReqRemark,@MpmPosReqBudget,@MpmPosReqAmount,@MpmPosId,@MpmBudTypeId,@MpmReqDeptId,@CreatedBy,@CreatedDate,@UpdatedBy,@UpdatedDate) ", con))
				{
					command.Parameters.Add(new SqlParameter("MpmReqId", mpmRequest.MpmReqId));
					command.Parameters.Add(new SqlParameter("MpmDeptId", mpmRequest.MpmDeptId));
					command.Parameters.Add(new SqlParameter("MpmReqNo", mpmRequest.MpmReqNo));
					command.Parameters.Add(new SqlParameter("MpmReqDate", mpmRequest.MpmReqDate));
					command.Parameters.Add(new SqlParameter("MpmReqStatus", mpmRequest.MpmReqStatus));
					command.Parameters.Add(new SqlParameter("MpmPosReqRemark", mpmRequest.MpmPosReqRemark));
					command.Parameters.Add(new SqlParameter("MpmPosReqBudget", mpmRequest.MpmPosReqBudget));
					command.Parameters.Add(new SqlParameter("MpmPosReqAmount", mpmRequest.MpmPosReqAmount));
					command.Parameters.Add(new SqlParameter("MpmPosId", mpmRequest.MpmPosId));
					command.Parameters.Add(new SqlParameter("MpmBudTypeId", mpmRequest.MpmBudTypeId));
					command.Parameters.Add(new SqlParameter("MpmReqDeptId", mpmRequest.MpmReqDeptId));
					command.Parameters.Add(new SqlParameter("CreatedBy", mpmRequest.CreatedBy));
					command.Parameters.Add(new SqlParameter("CreatedDate", mpmRequest.CreatedDate));
					command.Parameters.Add(new SqlParameter("UpdatedBy", mpmRequest.UpdatedBy));
					command.Parameters.Add(new SqlParameter("UpdatedDate", mpmRequest.UpdatedDate));
					command.ExecuteNonQuery();
				}
				con.Close();
			}
		}
	}
}